﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_Flow_GetPrePostInfo]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_Flow_GetPrePostInfo];
GO
CREATE PROCEDURE [dbo].[sproc_Flow_GetPrePostInfo]
    @StaffName nvarchar(300),
    @DocID int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @FlowRule int

DECLARE @Flow_ID int
DECLARE @Step_ID int
DECLARE @StaffID INT

DECLARE @SQL nvarchar(4000)

--得到人员职位ID
SELECT @StaffID = Staff_id FROM dbo.UDS_Staff WHERE Staff_Name = @StaffName;
IF @@rowcount <> 1
BEGIN
    raiserror(N'The specified user "%s" cannot be found.', 16, 1, @StaffName);
    return -1;
END;

--得到文档的当前流程ID和步骤ID
SELECT @Flow_ID = Flow_ID, @Step_ID = Step_ID FROM dbo.uds_flow_document WHERE Doc_ID = @DocID ;

--该步骤是否存在
IF EXISTS(SELECT 1 FROM dbo.uds_flow_step WHERE flow_id = @flow_id and step_id = @step_id)
BEGIN
        
    --根据条件判断，得到流转到的ID    
    DECLARE @FieldName         nvarchar(10)
    DECLARE @Compare         nvarchar(10)
    DECLARE @Compare_ValueStr    nvarchar(300)
    DECLARE @Compare_Value         INT
    DECLARE @To_Step_id         INT
    DECLARE @Flow_Rule        INT
    DECLARE @Jump_Flow_Rule        INT

    DECLARE @Result_Step_ID        INT
    
    SET @To_Step_id = -1        
    SET @Result_Step_ID = -1
    SET @Flow_Rule = -1
    SET @Jump_Flow_Rule = -1

    DECLARE @CommandAnd    bit
    DECLARE @Successful    bit

    SET @CommandAnd = 0
    SET @Successful = 0

    SELECT @Flow_Rule = Flow_Rule FROM dbo.uds_flow_step WHERE flow_id = @flow_id and step_id = @step_id;
    
    CREATE TABLE #D (doc_id int)

    --进入条件跳转模块
    PRINT '进入条件跳转模块'
    DECLARE cur_Jump Cursor FOR
        SELECT  FieldName,compare,comparevalue,to_step_id,flow_rule
            FROM dbo.uds_flow_jump
            WHERE flow_id = @flow_id and step_id = @step_id
            ORDER BY priority;

    OPEN cur_jump;
    --================================================
    FETCH NEXT FROM cur_jump INTO @FieldName,@Compare,@Compare_Value,@To_Step_id,@Jump_Flow_Rule

    IF @@FETCH_STATUS<>0 
        PRINT '没有条件'
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --跳转到自己的为与条件判断开始
        IF @To_Step_id = @Step_id AND @CommandAnd = 0
        BEGIN
            SET @CommandAnd = 1
            SET @Successful = 1 
        END

        --多个与条件判断中
        IF @CommandAnd = 1
        BEGIN
            --第一个不为跳转到自己的为最后一个与条件判断
            IF @To_Step_id <> @Step_id 
            BEGIN
                --与条件判断结束
                SET @CommandAnd = 0
            END

            IF @FieldName = 'caste'
            BEGIN
                SET @SQL = 'INSERT INTO #d (doc_id) SELECT 1 FROM uds_staff 
                        WHERE staff_id = ' + CONVERT(nvarchar(100),@StaffID)
                        + ' AND  CONVERT(INT,' + @FieldName + ')' + @Compare + CONVERT(nvarchar(100),@Compare_Value) 
            END
            ELSE            
                --条件判断
                SET @SQL = 'Insert INTO #d (doc_id)
                        SELECT doc_id 
                            FROM 
                                uds_flow_style_data 
                            WHERE doc_id = ' + CONVERT(nvarchar(100),@DocID)
                            + ' AND  CONVERT(INT,' + @FieldName + ')' + @Compare + CONVERT(nvarchar(100),@Compare_Value) 
    
            --PRINT @SQL
            EXEC (@SQL)    
        
            IF NOT EXISTS(SELECT 1 FROM #D)
            BEGIN
                SET @Successful = 0                
            END    

            --删除条件判断成功部分
            DELETE FROM #d    
            
            --最后一个与条件判断
            IF @CommandAnd = 0
            BEGIN
                --判断都成功
                IF @Successful = 1
                BEGIN
                    SET @Result_Step_ID = @To_Step_id
                    BREAK
                END
            END 
        END
        --单个或条件
        ELSE
        BEGIN
            IF @FieldName = 'caste'
            BEGIN
                SET @SQL = 'INSERT INTO #d (doc_id) SELECT 1 FROM uds_staff 
                        WHERE staff_id = ' + CONVERT(nvarchar(100),@StaffID)
                        + ' AND  CONVERT(INT,' + @FieldName + ')' + @Compare + CONVERT(nvarchar(100),@Compare_Value) 
            END
            ELSE
                --设置查询语句
                SET @SQL = 'Insert INTO #d (doc_id)
                        SELECT doc_id 
                            FROM 
                                uds_flow_style_data 
                            WHERE doc_id = ' + CONVERT(nvarchar(100),@DocID)
                            + ' AND  CONVERT(INT,' + @FieldName + ')' + @Compare + CONVERT(nvarchar(100),@Compare_Value) 
    
            --PRINT @SQL
            --执行语句
            EXEC (@SQL)    
            
            --条件判断成立
            IF EXISTS(SELECT 1 FROM #D)
            BEGIN
                SET @Result_Step_ID = @To_Step_id
                BREAK
            END
        END
        
        --下一条件
        FETCH NEXT FROM cur_jump INTO @FieldName,@Compare,@Compare_Value,@To_Step_id,@Jump_Flow_Rule
    END
            
    --================================================
    CLOSE cur_jump
    DEALLOCATE cur_jump

    DROP TABLE #D
    
    --一个没找到，进入默认循环
    IF @Result_Step_ID <0 
    BEGIN
        IF EXISTS(SELECT 1 
                    FROM 
                        uds_flow_step 
                    WHERE     flow_id = @flow_id
                        and step_id = @step_id+1
              )
        BEGIN
            SET @Result_Step_ID = @step_id + 1
            PRINT '顺序跳转' + CONVERT(nvarchar(300),@Result_Step_ID)
        END
    END
    ELSE

    BEGIN
            --注意：@Result_Step_ID = 0 为条件跳转时，结束流程
            PRINT '条件跳转' + CONVERT(nvarchar(300),@Result_Step_ID)
            SET @Flow_Rule = @Jump_Flow_Rule
    END
    
    --对于不是最后步骤的
    IF @Result_Step_ID>0
    BEGIN
        PRINT '流转文档'
        SELECT @Result_Step_ID AS Step_ID,@Flow_Rule Flow_Rule
        RETURN 0
    END
    ELSE
    BEGIN
        PRINT '结束文档'
        SELECT -1 AS Step_ID,-1 Flow_Rule
        RETURN -1
    END
        
END 
ELSE
    SELECT -2 AS Step_ID,-2 Flow_Rule

END
GO